package cnki.kg.demo;

import cnki.kg.demo.util.ExcelUtil;
import cnki.kg.demo.util.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;


@SpringBootTest
@WebAppConfiguration
public class CNMARC {
    @Autowired
    @Qualifier("mysqlJdbcTemplate")
    JdbcTemplate mysqlJdbcTemplate;

    @Test
    public void explainText() throws UnsupportedEncodingException {
        String txt = "\u001Fa235页\u001Fd26cm\u001E";
        String txt2 = "\u001Fa断路器\u001Fx液压操纵机构\u001Fx基本知识\u001E";
        String txt3 = "\u001Fa上海勘察设计志\u001Ff沈恭主编\u001Fg《上海勘察设计志》编纂委员会编\\\u001F9shang hai kan cha she ji zhi\u001E";
        String txt4 = "\u001Fa漫长的旅程榆树下的恋情\u001Ff(美)尤金・奥尼尔(E.O'neill)著\u001Fg欧阳基译\u001F9man chang de lu cheng yu shu xia de lian qing";
        String txt5 = "\u001Fa李欣频的私房50\u001F9li xin pin de si fang 50\u001Ff李欣频著\u001E";
        String[] split = txt.split("\u001F");
        if (StringUtil.isNotBlank(txt)) {
            String replace = txt.replace("\u001E", "");

        }
        if (split[0].equalsIgnoreCase("")) {
            System.out.println("ff");
        }
        System.out.println("");
    }

    @Test
    public void trans() {
        List<Map<String, Object>> rules = new ArrayList<>();
        String ruleSql = "select * from  marc_rule";
        rules = mysqlJdbcTemplate.queryForList(ruleSql);
        String countSql = "select max(id) from  bookinfo where cat_type_id=300";
        Integer totalCount = mysqlJdbcTemplate.queryForObject(countSql, Integer.class);
        if (totalCount == null) return;
        int pageSize = 50;
        for (int pageIndex = 0; pageIndex < totalCount; pageIndex++) {
            int start = pageIndex * pageSize + 1;
            if(pageIndex==0){
                start=0;
            }
            Integer end = (pageIndex + 1) * pageSize;
            System.out.println(String.format("开始查找bookId：[%s]-[%s],最后一本是：[%s]",start,end,totalCount));
            String indicatorRecordSql = String.format("select cat_type_id,tag,text,id from bookinfo where id between %s and %s", start, end);
            List<Map<String, Object>> objectRecord = mysqlJdbcTemplate.queryForList(indicatorRecordSql);
            if (objectRecord.size() > 0) {
                batchInsert(objectRecord,rules);
            }
        }
    }

    private void batchInsert(List<Map<String, Object>> objectRecords, List<Map<String, Object>> rules) {
        List<String> bookIds = objectRecords.stream().map(n -> n.get("id").toString()).distinct().collect(Collectors.toList());
        List<Map<String,String>> books=new ArrayList<>();
        for (String bookId : bookIds) {
            Map<String,String> bookModel=new HashMap<>();
            bookModel.put("BookId",bookId);
            bookModel.put("Cat_type","300");
            List<Map<String, Object>> bookItems = objectRecords.stream().filter(n -> n.get("id").toString().equals(bookId)).collect(Collectors.toList());
            if(bookItems.size()>0){
                String tags=bookItems.stream().map(n->n.get("tag").toString()).distinct().collect(Collectors.joining(","));
                bookModel.put("Tag",tags);
                for (Map<String, Object> bookItem : bookItems) {
                    explainText(bookItem,bookModel,rules);
                }
            }
            books.add(bookModel);
            System.out.println("处理数据："+bookId+"->"+bookModel.get("Title"));
        }
        List<BookInfo> bookInfos = HashMapToObject(books, BookInfo.class);
        saveBookRecords(bookInfos);
    }

    public void explainText(Map<String, Object> bookItem, Map<String,String> item,List<Map<String, Object>> rules) {
        String tag = bookItem.get("tag").toString();
        String txt = bookItem.get("text").toString();
        if (StringUtil.isNotBlank(txt)) {
            txt = txt.replace("\u001E", "");
        }
        String[] split = txt.split("\u001F");
        for (String s : split) {
            if (StringUtil.isBlank(s)) {
                continue;
            }
            String prefix = s.substring(0, 1);
            String value = s.substring(1, s.length());
            String dbField = getDbFieldName(tag, prefix, rules);
            if(StringUtil.isBlank(dbField)||StringUtil.isBlank(value)){
                continue;
            }
            item.put(dbField,value);
        }
    }

    private String getDbFieldName(String tag, String prefix, List<Map<String, Object>> rules) {
        List<Map<String, Object>> collect = rules.stream().filter(n -> n.get("Tag").toString().equalsIgnoreCase(tag) && n.get("prefix").toString().equals(prefix)).collect(Collectors.toList());
        if (collect.size() > 0) {
            return collect.get(0).get("field").toString();
        }
        return "";
    }

    private void saveBookRecords(List<BookInfo> bookInfos) {
        Field[] declaredFields = BookInfo.class.getDeclaredFields();
        List<Field> fields = new ArrayList<>(Arrays.asList(declaredFields));
        String dbFields = fields.stream().map(Field::getName).collect(Collectors.joining(","));
        String valuesParam = bookInfos.stream().map(n ->
                String.format("('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'," +
                                "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'," +
                                "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'," +
                                "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'," +
                                "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'," +
                                "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
                        n.getGeneralProcessingData(),n.getTextLanguage(),n.getMiddleLanguage(),
                        n.getOriginalLanguage(),n.getPublishCountry(),n.getPublishArea(),
                        n.getBookCodingData(),n.getTextDataCode(),n.getTitle(),
                        n.getGeneralDataCode(),n.getAnotherAuthorTitle(),n.getParallelTitle(),
                        n.getSubtitle(),n.getFirstResponsiblePerson(),n.getOtherResponsiblePerson(),
                        n.getFascicleCode(),n.getFascicleName(),n.getVolumeNumber(),
                        n.getParallelTitleLanguage(),n.getTitleChinesePinyin(),n.getSubtitleChinesePinyin(),
                        n.getFirstResponsiblePersonPinyin(),n.getFascicleNamePinyin(),n.getVersionDescription(),
                        n.getPublicationArea(),n.getPublisherName(),n.getPublishDate(),
                        n.getBookCodeAndNumberUnit(),n.getBookOtherDetail(),n.getBookSize(),
                        n.getBookFile(),n.getSerialTitle(),n.getParallelTitle(),
                        n.getSeriesSubtitle(),n.getSeriesResponsiblePerson(),n.getGeneralNotes(),
                        n.getContentNotes(),n.getMainHeading(),n.getThematicSubdivision(),
                        n.getRegionalSubdivision(),n.getChronologicalSubdivision(),n.getCategoryCode(),
                        n.getEdition(),n.getEquivalentResponsiblePerson(),n.getTitleOtherText(),
                        n.getCopyrightLiability(),n.getItemElementsPinyin(),n.getSecondaryResponsiblePerson(),
                        n.getEquivalentResponsibleGroupName(),n.getCountryCode(),n.getOrganizationCode(),
                        n.getHandDate(),n.getCollectionHallCode(),n.getLoginCode(),
                        n.getBookNumber(),n.getNumberOfCopies(),n.getEntryVolumePeriod(),
                        n.getChronologicalRange(),n.getTag(),n.getBookId(),n.getCat_type()
                )).collect(Collectors.joining(","));
        String sql = String.format(" insert into bookinfo_struct (%s) values %s", dbFields,valuesParam);
        mysqlJdbcTemplate.execute(sql);
    }


    public <T> List<T> HashMapToObject(List<Map<String, String>> maps, Class<T> type) {
        try {
            List<T> list = new ArrayList<T>();
            for (Map<String, String> r : maps) {
                T t = type.newInstance();
                Iterator iter = r.entrySet().iterator();// 该方法获取列名.获取一系列字段名称.例如name,age...
                while (iter.hasNext()) {
                    Map.Entry entry = (Map.Entry) iter.next();// 把hashmap转成Iterator再迭代到entry
                    String key = entry.getKey().toString(); // 从iterator遍历获取key
                    String value = entry.getValue().toString(); // 从hashmap遍历获取value
                    if(value.equals("1689")){
                        System.out.println(value);
                    }
                    Field field = type.getDeclaredField(key);// 获取field对象
                    if (field != null) {
                        field.setAccessible(true);
                        if (field.getType() == String.class) {
                            value=value.replace("\\","\\\\");
                            value=value.replaceAll("'","\\'");
                            value=value.replace("'","\\'");
                            field.set(t, value);// 设置值
                        } else if (field.getType() == int.class || field.getType() == Integer.class) {
                            if (StringUtil.isBlank(value)) {
                                field.set(t, 0);// 设置值
                            } else {
                                field.set(t, Integer.parseInt(value));// 设置值
                            }

                        } else {
                            field.set(t, value);// 设置值
                        }
                    }

                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    @Test
    public void updateNull(){
        Field[] declaredFields = BookInfo.class.getDeclaredFields();
        List<Field> fields = new ArrayList<>(Arrays.asList(declaredFields));
        for (Field field : fields) {
            String updateSql=String.format("update bookinfo_struct set %s='' where %s='null'",field.getName(),field.getName());
            mysqlJdbcTemplate.execute(updateSql);
        }

    }
    @Test
    public void createRule() throws IOException {
        String filePath = "D:\\360MoveData\\Users\\Tan\\Desktop\\dd\\rule.xlsx";
        File file = new File(filePath);
        String fileName = file.getName();
        InputStream in = null;
        in = new FileInputStream(file);
        Workbook workbook = null;
        if (ExcelUtil.isExcel2007(fileName)) {
            workbook = new XSSFWorkbook(in);
        } else {
            workbook = new HSSFWorkbook(in);
        }
        // 有多少个sheet
        int sheets = workbook.getNumberOfSheets();
        List<HashMap<String, String>> mps = new ArrayList<>();
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int rowSize = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < rowSize; j++) {
                if (j == 0) continue;
                Row row = sheet.getRow(j);
                int cellSize = row.getPhysicalNumberOfCells();
                String tag = "";
                String labels = "";
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell0 = row.getCell(0);
                    tag = cell0.getStringCellValue();
                }
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    Cell cell1 = row.getCell(1);
                    labels = cell1.getStringCellValue();
                }
                List<HashMap<String, String>> records = buildRecords(tag, labels);
                mps.addAll(records);
            }
        }
        if (mps.size() > 0) {
            saveRecords(mps);
        }

    }

    private void saveRecords(List<HashMap<String, String>> records) {
        String valuesParam = records.stream().map(n -> String.format("('%s','%s','%s','%s')", n.get("tag"), n.get("label"), n.get("prefix"), n.get("catType"))).collect(Collectors.joining(","));
        String sql = String.format(" insert into marc_rule (tag,label,prefix,catType) values %s", valuesParam);
        mysqlJdbcTemplate.execute(sql);
    }

    private List<HashMap<String, String>> buildRecords(String tag, String labels) {
        List<HashMap<String, String>> mps = new ArrayList<>();
        String[] splitArr = labels.split("@");
        for (String s : splitArr) {
            if (StringUtil.isBlank(s)) continue;
            HashMap<String, String> mp = new HashMap<>();
            mp.put("tag", tag);
            String prefix = s.substring(0, 1);
            String label = s.substring(1, s.length());
            mp.put("label", label);
            mp.put("prefix", prefix);
            mp.put("catType", "300");
            mps.add(mp);
        }
        return mps;
    }

    //解析指定MARC数据，返回SortedList<>
    private LinkedHashMap<String, String> GetData(String MarcText) throws UnsupportedEncodingException {
        int MarcTextLength = Integer.parseInt(MarcText.substring(0, 5));//MARC记录文本总长度
        int _ConPos = Integer.parseInt(MarcText.substring(12, 5));//数据字段区起始地址
        int _Count = (_ConPos - 24) / 12;//字段个数
        String _Address = MarcText.substring(24, _ConPos - 24 - 1);//地址目次区字符串    
        String _Contents = MarcText.substring(_ConPos);//数据字段区字符串   

        //---------开始解析字段数据

        String[] _Keys = new String[_Count];//字段编号
        String[] _Values = new String[_Count];//字段值

        for (int i = 0; i < _Count; i++) {
            String _Num = _Address.substring(i * 12, 3);//字段编号
            int _Len = Integer.parseInt(_Address.substring(i * 12 + 3, 4));//字段对应的数据区长度
            int _Pos = Integer.parseInt(_Address.substring(i * 12 + 7, 5));//字段对应的数据区开始位置
            _Keys[i] = _Address.substring(i * 12, 3);//填充字段编号
            int _NewPos = GetCharLength(_Contents, _Pos + 2);//获取在中英文混排MARC文本中的实际位置
            int _NewLen = GetCharLength(_Contents, _Pos + 2 + _Len) - _NewPos;//获取实际截取长度
            _Values[i] = _Contents.substring(_NewPos, _NewLen);//填充字段值
        }

        LinkedHashMap<String, String> _SL = new LinkedHashMap<String, String>();
        for (int i = 0; i < _Count; i++) {
            if (_SL.containsKey(_Keys[i])) {
                _SL.replace(_Keys[i], " 【！】 " + _Values[i]);
            } else {
                _SL.put(_Keys[i], _Values[i]);
            }
        }

        return _SL;
    }

    //字段编号对应的字段名称
    private LinkedHashMap<String, String> GetName() {
        LinkedHashMap<String, String> _SL = new LinkedHashMap<String, String>();
        _SL.put("001", "控制号");
        _SL.put("005", "最近一次作业的日期和时间");
        _SL.put("010", "国际标准书号");
        _SL.put("011", "国际标准连续出版物号");
        _SL.put("016", "国际标准音像编码");
        _SL.put("091", "统一书刊号");
        _SL.put("094", "标准号");

        _SL.put("100", "一般处理数据");
        _SL.put("101", "文献语种");
        _SL.put("102", "出版/制作国别");
        _SL.put("105", "专著性文字资料");
        _SL.put("106", "文字资料 — 形态特征");
        _SL.put("110", "连续出版物");

        _SL.put("200", "题名与责任说明");
        _SL.put("205", "版本说明");
        _SL.put("207", "资料特殊细节项：连续出版物编号");
        _SL.put("210", "出版发行等");
        _SL.put("215", "载体形态项");
        _SL.put("225", "丛编项");

        _SL.put("300", "一般性附注");
        _SL.put("303", "著录信息一般性附注");
        _SL.put("304", "题名与责任说明附注");
        _SL.put("305", "版本与书目沿革附注");
        _SL.put("306", "出版发行附注");
        _SL.put("307", "载体形态附注");
        _SL.put("308", "丛编附注");
        _SL.put("310", "装订与获得方式附注");
        _SL.put("311", "连接字段附注");
        _SL.put("312", "相关题名附注");
        _SL.put("314", "知识责任附注");
        _SL.put("320", "内部书目/索引附注");
        _SL.put("324", "原作版本附注");
        _SL.put("325", "复制品附注");
        _SL.put("327", "内容附注");
        _SL.put("328", "学位论文附注");
        _SL.put("330", "提要或文摘附注");
        _SL.put("333", " 用户/使用对象附注");

        _SL.put("410", "丛编");
        _SL.put("421", "补编");
        _SL.put("422", "正编");
        _SL.put("423", "合订");
        _SL.put("430", "继承");
        _SL.put("440", "由…继承");
        _SL.put("451", "同一载体的其他版本");
        _SL.put("452", " 不同载体的其他版本");
        _SL.put("461", "总集");
        _SL.put("462", "分集");
        _SL.put("488", "其他相关作品");

        _SL.put("500", "统一题名");
        _SL.put("510", "并列正题名");
        _SL.put("512", "封面题名");
        _SL.put("513", "附加题名页题名");
        _SL.put("514", "卷端题名");
        _SL.put("515", "逐页题名");
        _SL.put("516", "书脊题名");
        _SL.put("517", "其它题名");
        _SL.put("518", " 现行标准拼写形式的题名");
        _SL.put("532", "展开题名");
        _SL.put("540", " 编目员补充的附加题名");

        _SL.put("600", "个人名称主题");
        _SL.put("601", "团体名称主题");
        _SL.put("602", "家族名称主题");
        _SL.put("604", "名称与题名主题");
        _SL.put("605", "题名主题");
        _SL.put("606", "论题主题");
        _SL.put("607", "地理名称主题");
        _SL.put("610", "非控主题词");
        _SL.put("690", " 中国图书馆分类法分类号");
        _SL.put("692", " 中国科学院图书馆图书分类法分类号");
        _SL.put("694", " 中国人民大学图书馆图书分类法分类号");
        _SL.put("696", "国内其它分类号");

        _SL.put("701", "个人名称 — 等同知识责任");
        _SL.put("702", "个人名称 — 次要知识责任");
        _SL.put("711", "团体名称 — 等同知识责任");
        _SL.put("712", "团体名称 — 次要知识责任");
        _SL.put("721", "家族名称 — 等同知识责任");
        _SL.put("722", "家族名称 — 次要知识责任");
        _SL.put("730", "名称—非规范名称");

        _SL.put("801", "记录来源");
        _SL.put("802", "ISSN中心");
        _SL.put("856", "电子资源定位与检索");

        _SL.put("920", "馆藏信息");
        _SL.put("998", " 书目记录所属成员馆代码");

        return _SL;
    }

    //获得字符的长度
    private int GetCharLength(String MarcText, int _Len) throws UnsupportedEncodingException {
        byte[] sArr = MarcText.getBytes();
        int i = 0, j = 0;
        while (i < _Len && j < MarcText.length() - 1) {
            sArr = MarcText.getBytes(MarcText.substring(j, 1));
            if (sArr.length == 2) {
                i += 2;
            } else {
                i += 1;
            }
            j += 1;
        }
        return j;
    }

}
